package edu.fudan.cse.medlab.event.filter;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.LinkedList;


public class DB {
	private static Connection connection;
	private static int recordCount;

	 private static void open() {
	    try {
	    	Settings setting = new Settings();
	    	Class.forName("com.mysql.jdbc.Driver").newInstance();
			connection = java.sql.DriverManager.getConnection(
					"jdbc:mysql://"+setting.host+":3306/"+setting.dbname
					+"?useUnicode=true&characterEncoding=utf8",setting.username,setting.password);	
	       }
	    catch (Exception e) {
	        e.printStackTrace();
	    }
	 }
	
//	public static void open(String host,String dbname,String username,String password){
//		host = host.trim();
//		dbname = dbname.trim();
//		username = username.trim();
//		password = password.trim();
//		try{
//			Class.forName("com.mysql.jdbc.Driver").newInstance();
//			connection = java.sql.DriverManager.getConnection("jdbc:mysql://"+host+":3306/"+dbname+"?useUnicode=true&characterEncoding=utf8",username,password);
//			
//		}
//		catch(Exception e){
//			e.printStackTrace();
//		}
//	}
//	
	public static void close()
	{
		try{
			if(connection!=null)
			{
				connection.close();
			}
		}
		catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	
	public static boolean writeLog(Timestamp time, String info) throws Exception{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("insert into logger values(?,?,id)");
		preparedStatement.setTimestamp(1, time);
		preparedStatement.setString(2,info);
		if (preparedStatement.executeUpdate()==1)
			return true;
		else
			return false;
	}
	
	public static ResultSet readLog() throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("select log from logger");
		ResultSet resultSet=preparedStatement.executeQuery();
		resultSet.close();
		preparedStatement.close();
		return resultSet;
	}
	
	public static ResultSet readLogAfterTime(Timestamp time) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("select log from logger where timestamp >"+time);
		ResultSet resultSet=preparedStatement.executeQuery();
		resultSet.close();
		preparedStatement.close();
		return resultSet;
	}
	
	public static boolean insertToLocation(String location, String address, String des)throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("insert into location values(LID,?,?,?,0,0)");
		preparedStatement.setString(1,location);
		preparedStatement.setString(2,address);
		preparedStatement.setString(3,des);
		if (preparedStatement.executeUpdate()==1){
			preparedStatement.close();
			return true;
		}
		else{
			preparedStatement.close();
			return false;
		}
	}
	
	public static boolean insertToAlias(String location,String alias) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		if(alias.equals(""))
			return false;
		PreparedStatement preparedStatement = connection.prepareStatement("select * from location where Name =?");
		preparedStatement.setString(1,location);
		ResultSet resultSet=preparedStatement.executeQuery();
		resultSet.first();
		int LID = resultSet.getInt("LID");
		
		preparedStatement = connection.prepareStatement("insert into alias values(AID,?,?)");
		preparedStatement.setInt(1,LID);
		preparedStatement.setString(2,alias);
		resultSet.close();
		if (preparedStatement.executeUpdate()==1){
			preparedStatement.close();
			return true;
		}
		else{
			preparedStatement.close();
			return false;
		}
	}
	

	public static String[] getAllLocation() throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("select * from Location");
		ResultSet resultSet=preparedStatement.executeQuery();
		String lname;
		String all = "";
		while (resultSet.next()) {
			lname= resultSet.getString("Name");
			all += lname+"?";
		}
		PreparedStatement preparedStatement2 = connection.prepareStatement("select * from Alias");
		ResultSet resultSet2=preparedStatement2.executeQuery();
		while (resultSet2.next()) {
			lname= resultSet2.getString("Name");
			all += lname+"?";
		}
		all = all.substring(0,all.length()-1);
		resultSet2.close();
		resultSet.close();
		preparedStatement.close();
		return all.split("\\?");
	}

	
	public static String getLocationByLID(int lid) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("select * from Location where LID=?");
		preparedStatement.setInt(1,lid);
		ResultSet resultSet=preparedStatement.executeQuery();
		resultSet.next();
		String s = resultSet.getString("Name");
		resultSet.close();
		return s;
	}
	
	public static ResultSet getAllInfo() throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("select * from event");
		ResultSet resultSet=preparedStatement.executeQuery();
		return resultSet;
	}
	
	public static ResultSet getAllTitle() throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("select title from Event");
		ResultSet resultSet=preparedStatement.executeQuery();
		return resultSet;
	}
	
	public static String[] getAllDistinctTitle(int k) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		String title="",temp;
		PreparedStatement preparedStatement = connection.prepareStatement("select distinct eventName from event");
		ResultSet resultSet=preparedStatement.executeQuery();
		LinkedList<String> titles = new LinkedList();
		while (resultSet.next()) {
			temp= resultSet.getString("eventName");
			if(temp.length()<=2)
				continue;
			if(!temp.equals("&gt"))
				titles.add(temp);
		}
		
		
		resultSet.close();
		preparedStatement.close();
		return titles.toArray(new String[0]);
		//return title.split("\\?");
	}
	
	public static ResultSet getAllDistinctTitle() throws SQLException{
		PreparedStatement preparedStatement = connection.prepareStatement("select distinct title from Event");
		ResultSet resultSet=preparedStatement.executeQuery();
		return resultSet;
	}
	
	public static int getEventCount(int lid) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("select * from Location where LID=?");
		preparedStatement.setInt(1,lid);
		ResultSet resultSet=preparedStatement.executeQuery();
		if(DB.getRecordCount(resultSet) == 0)
			return -1;
		
		PreparedStatement preparedStatement2 = connection.prepareStatement("select  title from Event where LID=?");
		preparedStatement2.setInt(1, lid);
		ResultSet resultSet2=preparedStatement2.executeQuery();
		int k = DB.getRecordCount(resultSet2);
		resultSet2.close();
		resultSet.close();
		preparedStatement.close();
		return k;
	}
	
	public static int getDistinctEventCount(int lid) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("select * from Location where LID=?");
		preparedStatement.setInt(1,lid);
		ResultSet resultSet=preparedStatement.executeQuery();
		if(DB.getRecordCount(resultSet) == 0)
			return -1;
		
		PreparedStatement preparedStatement2 = connection.prepareStatement("select distinct title from Event where LID=?");
		preparedStatement2.setInt(1, lid);
		ResultSet resultSet2=preparedStatement2.executeQuery();
	
		int k = DB.getRecordCount(resultSet2);
		resultSet2.close();
		resultSet.close();
		preparedStatement.close();
		return k;
	}
	
	public static boolean addURL(String url) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("select * from url where url=?");
		preparedStatement.setString(1,url);
		ResultSet resultSet=preparedStatement.executeQuery();
		if(DB.getRecordCount(resultSet)==1)
			return false;
		//(40,'sh.nihaopiao.com',173,80,4,196658,NULL,10,NULL,1,'0',2,255,0,0,0,0),
		PreparedStatement preparedStatement2 = connection.prepareStatement("insert into url values(urlid,?,0,0,0,0,NULL,0,0,0,0,0,0,0,0,0,0)");
		preparedStatement2.setString(1,url);
		
		preparedStatement.close();
		if (preparedStatement2.executeUpdate()==1){
			preparedStatement2.close();
			return true;
		}
		else{
			preparedStatement2.close();
			return false;
		}
	}
	
	public static boolean updateURLref1(String s,int k) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("update url set ref1=? where url=?");
		preparedStatement.setInt(1,k);
		preparedStatement.setString(2,s);
		if(preparedStatement.executeUpdate()==1){
			preparedStatement.close();
			return true;
		}
		else{
			preparedStatement.close();
			return false;
		}
	}
	
	public static boolean updateURLref2(String s,int k) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("update url set ref2=? where url=?");
		preparedStatement.setInt(1,k);
		preparedStatement.setString(2,s);
		if(preparedStatement.executeUpdate()==1){
			preparedStatement.close();
			return true;
		}
		else {
			preparedStatement.close();
			return false;
		}
	}
	
	public static boolean updateURLref3(String s,int k) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("update url set ref3=? where url=?");
		preparedStatement.setInt(1,k);
		preparedStatement.setString(2,s);
		if(preparedStatement.executeUpdate()==1){
			preparedStatement.close();
			return true;
		}
		else{
			preparedStatement.close();
			return false;
		}
	}
	
	public static boolean updateURLref() throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("update url set ref= (ref1+ref2+ref3)");
		if(preparedStatement.executeUpdate()==1){
			preparedStatement.close();
			return true;
		}
		else {
			preparedStatement.close();
			return false;
		}
	}
	
	
	public static boolean updateURLpagerank(String s,int k) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("update url set pagerank=? where url=?");
		preparedStatement.setInt(1,k);
		preparedStatement.setString(2,s);
		if(preparedStatement.executeUpdate()==1){
			preparedStatement.close();
			return true;
		}
		else{
			preparedStatement.close();
			return false;
		}
	}
	
	public static boolean updateURLalexa(String s,int k) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("update url set alexa=? where url=?");
		preparedStatement.setInt(1,k);
		preparedStatement.setString(2,s);
		if(preparedStatement.executeUpdate()==1){
			preparedStatement.close();
			return true;
		}
		else{
			preparedStatement.close();
			return false;
		}
	}
	
	public static boolean updateByLogFile(String s,String  size, int pagecount, int minutes) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("update url set size=?,pagecount=?,minutes=? where url=?");
		preparedStatement.setString(1,size);
		preparedStatement.setInt(2,pagecount);
		preparedStatement.setInt(3, minutes);
		preparedStatement.setString(4, s);
		if(preparedStatement.executeUpdate()==1)
			return true;
		else 
			return false;
	}
	
	public static boolean updateURLFilterPage(String s,int filterpage) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("update url set targetcount=? where url=?");
		preparedStatement.setInt(1,filterpage);
		preparedStatement.setString(2, s);
		if(preparedStatement.executeUpdate()==1){
			preparedStatement.close();
			return true;
		}
		else{
			preparedStatement.close();
			return false;
		}
	}
	
	public static boolean updateURLPageCount(String s,int pagecount) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("update url set pagecount=? where url=?");
		System.out.println(s+"   "+pagecount);
		preparedStatement.setInt(1,pagecount);
		preparedStatement.setString(2, s);
		if(preparedStatement.executeUpdate()==1){
			preparedStatement.close();
			return true;
		}
		else{
			preparedStatement.close();
			return false;
		}
	}
	
	public static ResultSet getURL() throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("select url,pagerank,alexa from url");
		ResultSet resultSet=preparedStatement.executeQuery();
		return resultSet;
	}
	
	public static ResultSet getURLreflt(int k) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("select url from url where ref1>="+k+" order by -ref1");
		ResultSet resultSet=preparedStatement.executeQuery();
		return resultSet;
	}
	
	public static ResultSet getURLByRef() throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("select url from url  order by -ref");
		ResultSet resultSet=preparedStatement.executeQuery();
		return resultSet;
	}
	
	public static boolean setState(int k) throws SQLException{
	//	if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("update state set state=?");
		preparedStatement.setInt(1, k);
		if(preparedStatement.executeUpdate()==1){
			preparedStatement.close();
			return true;
		}
		else{
			preparedStatement.close();
			return false;
		}	
	}
	
	public static String[] getSitesByRef(String ref, int count) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("select url from url order by -"+ref);
		ResultSet resultSet=preparedStatement.executeQuery();
		String[] sites  = new String[count];
		for (int i = 0; i < count; i++) {
			resultSet.next();
			sites[i] = resultSet.getString("url");
		}
		resultSet.close();
		preparedStatement.close();
		return sites;
	}
	
	public static int getEventNumForSite(String site) throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		site = site.replace("\\", "\\\\");
		PreparedStatement preparedStatement = connection.prepareStatement("select count(distinct event_id) from url_event where  url like BINARY '%" + site + "%'");
		ResultSet resultSet=preparedStatement.executeQuery();
		resultSet.next();
		int k =resultSet.getInt("count(distinct event_id)");
		resultSet.close();
		preparedStatement.close();
		return k;
	}
	
	public static int getState() throws SQLException{
		if(connection==null || connection.isClosed())
			open();
		PreparedStatement preparedStatement = connection.prepareStatement("select * from state");
		ResultSet resultSet=preparedStatement.executeQuery();
		resultSet.next();
		int k = resultSet.getInt("state");
		resultSet.close();
		preparedStatement.close();
		return k;
	}
	
	
	public static int getRecordCount(ResultSet resultSet)
	  {
	    try
	    {
	      if(resultSet.last())
	      {
	        recordCount = resultSet.getRow();
	      }
	      else
	      {
	        recordCount = 0;
	      }
	    }
	    catch(java.sql.SQLException e){}
	    finally{
	      return recordCount;
	    }
	  }
	
}
